package banco.cmv;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import banco.Banco;
import dominio.Cliente;
import dominio.Tipo_Planilha;
import dominio.cmv.Informativo_Fiscal;
import dominio.despant.Despesa_Antecipada;
import fachada.cmv.FachadaCMV;

public class Res_Info_FiscalDAO implements FachadaCMV {

	@SuppressWarnings("finally")
	public void insertRes_Info_Fiscal(Informativo_Fiscal info, Tipo_Planilha tp, Cliente c) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("insert into res_info_fiscal values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
	            stmt.setFloat(1, info.getEstoque_inicial());
	            stmt.setFloat(2, info.getEntradas());
	            stmt.setFloat(3, info.getEstoque_final());
	            stmt.setFloat(4, info.getCusto());
	            stmt.setFloat(5, info.getSaidas());
	            stmt.setFloat(6, info.getValor_adicionado());
	            stmt.setFloat(7, info.getVradic_saidas());
	            stmt.setFloat(8, info.getDespesas_informativo());
	            stmt.setFloat(9, info.getAdicionado_despesas());
	            stmt.setFloat(10, info.getReceita_servico());
	            stmt.setFloat(11, info.getCampo_adicional());
	            stmt.setInt(12, tp.getId());
	            stmt.setString(13, c.getCnpj().trim());
	            stmt.executeUpdate();
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        
	        catch (Exception e) {
	        	e.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
	        }
	    }
	
	
	@SuppressWarnings("finally")
	public Informativo_Fiscal selectRes_Info_Fiscal(Tipo_Planilha tp, Cliente c, float custo, float estoque_inicial) throws ClassNotFoundException, IOException {
	    
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Informativo_Fiscal info = new Informativo_Fiscal();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from res_info_fiscal where cnpj_cliente = ? and id_tipo_planilha = ? and custo = ? and estoque_inicial = ?");
	        stmt.setString(1, c.getCnpj().trim());
	        stmt.setInt(2, tp.getId());
	        stmt.setFloat(3, custo);
	        stmt.setFloat(4, estoque_inicial);
	        rs = stmt.executeQuery();
	        while (rs.next()){	            
	            info.setId(rs.getInt("id"));	            
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return info;
	    }
	}
	
	@SuppressWarnings("finally")
	public Collection selectRes_Info_Fiscal_Cliente(Cliente c, boolean valida) throws ClassNotFoundException, IOException {
	    
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Collection<Informativo_Fiscal> res = new ArrayList<Informativo_Fiscal>();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from res_info_fiscal where cnpj_cliente = ? and valida = ?");
	        stmt.setString(1, c.getCnpj().trim());
	        stmt.setBoolean(2, valida);	        
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	Informativo_Fiscal info = new Informativo_Fiscal();
	        	info = new Informativo_Fiscal();
	            info.setId(rs.getInt("id"));
	            info.setAdicionado_despesas(rs.getFloat("adicionado_despesas"));
	            info.setCampo_adicional(rs.getFloat("campo_adicional"));
	            info.setCusto(rs.getFloat("custo"));
	            info.setDespesas_informativo(rs.getFloat("despesas_informativo"));
	            info.setEntradas(rs.getFloat("entradas"));
	            info.setEstoque_final(rs.getFloat("estoque_final"));
	            info.setEstoque_inicial(rs.getFloat("estoque_inicial"));
	            info.setReceita_servico(rs.getFloat("receita_servico"));
	            info.setSaidas(rs.getFloat("saidas"));
	            info.setValor_adicionado(rs.getFloat("valor_adicionado"));
	            info.setVradic_saidas(rs.getFloat("vradic_saidas"));
	            res.add(info);
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return res;
	    }
	}
	
	@SuppressWarnings("finally")
	public Informativo_Fiscal selectRes_Info_Fiscal_ById(int id) throws ClassNotFoundException, IOException {
	    
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Informativo_Fiscal info = new Informativo_Fiscal();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from res_info_fiscal where id = ?");
	        stmt.setInt(1, id);
	        rs = stmt.executeQuery();
	        while (rs.next()){
	            info.setId(rs.getInt("id"));
	            info.setAdicionado_despesas(rs.getFloat("adicionado_despesas"));
	            info.setCampo_adicional(rs.getFloat("campo_adicional"));
	            info.setCusto(rs.getFloat("custo"));
	            info.setDespesas_informativo(rs.getFloat("despesas_informativo"));
	            info.setEntradas(rs.getFloat("entradas"));
	            info.setEstoque_final(rs.getFloat("estoque_final"));
	            info.setEstoque_inicial(rs.getFloat("estoque_inicial"));
	            info.setReceita_servico(rs.getFloat("receita_servico"));
	            info.setSaidas(rs.getFloat("saidas"));
	            info.setValor_adicionado(rs.getFloat("valor_adicionado"));
	            info.setVradic_saidas(rs.getFloat("vradic_saidas"));
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return info;
	    }
	}
	
	@SuppressWarnings("finally")
	public void updateInformativoApropriado(Informativo_Fiscal info) throws ClassNotFoundException, IOException {
	    
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;	   
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("update res_info_fiscal set valida = ? where id = ?");
	        stmt.setBoolean(1, info.isApropriada());
	        stmt.setFloat(2, info.getId());
	        stmt.executeUpdate();
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    
	    catch (Exception e) {
	    	e.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	    }
	}

}
